package cn.bijy;

import cn.bijy.dao.UserDao;
import cn.bijy.io.Resources;
import cn.bijy.pojo.User;
import cn.bijy.sqlsession.SqlSession;
import cn.bijy.sqlsession.SqlSessionFactory;
import cn.bijy.sqlsession.SqlSessionFactoryBuilder;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.io.InputStream;
import java.sql.*;
import java.util.List;

/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args ) throws Exception {
        InputStream resourcesAsStream = Resources.getResourcesAsStream("sqlMapConfig.xml");
        SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
        SqlSessionFactory build = sqlSessionFactoryBuilder.build(resourcesAsStream);
        SqlSession sqlSession = build.openSession();
        User user = new User();
        user.setId(5);
        user.setUsername("钱七");
//        User o = sqlSession.selectOne("user.selectOne", user);
//        System.out.println(o);
//        List<User> objects = sqlSession.selectList("user.selectList", null);
//        objects.forEach(System.out::println);

        UserDao mapper = sqlSession.getMapper(UserDao.class);
//        List<User> users = mapper.selectAll();
//        users.forEach(System.out::println);
//        User user1 = mapper.selectByCondiction(user);
//        System.out.println(user1);

        //mapper.save(user);

        mapper.updateById(user);

        User user1 = mapper.selectByCondiction(user);
        System.out.println(user1);

    }

    @Test
    public void test() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String dbUrl = "jdbc:mysql://localhost:3306/persistence-test?characterEncoding=utf-8";
        String username = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(dbUrl, username, password);

        String sql = "select * from user where username = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setString(1, "张三");
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("username");
            System.out.println(id+"===="+name);
        }
        connection.close();
    }

    @Test
    public void test1() throws ClassNotFoundException, SQLException, PropertyVetoException {

        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/persistence-test?characterEncoding=utf-8");
        dataSource.setUser("root");
        dataSource.setPassword("123456");

        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "select * from user where username = ?";
        User user = queryRunner.query(sql, new BeanHandler<User>(User.class), "张三");
        System.out.println(user);
    }

    @Test
    public void test3() throws ClassNotFoundException, SQLException, PropertyVetoException {

        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/persistence-test?characterEncoding=utf-8");
        dataSource.setUser("root");
        dataSource.setPassword("123456");;
        String sql = "select * from user";
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<User> query = jdbcTemplate.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                int id = resultSet.getInt("id");
                String name = resultSet.getString("username");
                user.setId(id);
                user.setUsername(name);
                return user;
            }
        });

        System.out.println(query);
    }

    @Test
    public void test4() throws ClassNotFoundException, SQLException{
        Configuration configuration = new Configuration().configure();
        SessionFactory sessionFactory = configuration.buildSessionFactory();
        Session session = sessionFactory
                .openSession();
//        Transaction transaction = session.getTransaction();
//        transaction.begin();
//        User user = new User();
//        user.setId(4);
//        user.setUsername("王五");
//        session.save(user);
//        transaction.commit();
//        session.close();

        Query query = session.createQuery("from cn.bijy.pojo.User");
        List list = query.list();
        System.out.println(list);

    }
}
